# -*- encoding: utf-8 -*-
"""
@File        : data_access_object.py
@License     : (C)Copyright 2021 LeeRoc (cn) Lipeng
@Modify Time : 2021/7/30 11:37 下午
@Author      : LeeRoc
@Version     : 1.0
@Desciption  :
"""

import sys

sys.path.append('../')

import sqlite3


class host_table:
    def __init__(self):
        try:
            self.__conn = sqlite3.connect("../database/host_packet.db")
            self.__cur = self.__conn.cursor()
            self.__create_host_table()
        except Exception as e:
            raise e

    def __create_host_table(self):
        """
        创建table
        """
        create_host_table_sql = "create table host(" \
                                "    ip varchar(15) not null primary key unique ," \
                                "    port_1 varchar not null ," \
                                "    port_2 varchar not null ," \
                                "    port_1_status varchar ," \
                                "    port_2_status varchar ," \
                                "    net_status varchar ," \
                                "    internal_memory_size varchar ," \
                                "    cpu_status varchar" \
                                ");"
        try:
            self.__cur.execute(create_host_table_sql)
            self.__conn.commit()
        except Exception as e:
            print(e)

    def close(self):
        self.__conn.close()

    def insert_host(self, ip, port1, port2, port1_status, port2_status, net_status, internal_msize, cpu_status):
        insert_host_sql = "insert into host values (\'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\')" \
                          % (ip, port1, port2, port1_status, port2_status, net_status, internal_msize, cpu_status)
        try:
            self.__cur.execute(insert_host_sql)
            self.__conn.commit()
        except Exception as e:
            self.__conn.rollback()
            print(e)

    def update_host_portstatus(self, hostip, status, port=1):
        if port == 1:
            update_host_portstatus_sql = "update host set port_1_status=\'%s\' where ip=\'%s\'" % (status, hostip)
        else:
            update_host_portstatus_sql = "update host set port_2_status=\'%s\' where ip=\'%s\'" % (status, hostip)

        try:
            self.__cur.execute(update_host_portstatus_sql)
            self.__conn.commit()
        except Exception as e:
            self.__conn.rollback()
            print(e)

    def update_host_netstatus(self, hostip, status='ok'):
        if status == 'ok':
            update_host_netstatus_sql = "update host set net_status=\'%s\' where ip=\'%s\'" % (status, hostip)
        else:
            update_host_netstatus_sql = "update host set net_status=\'%s\' where ip=\'%s\'" % (status, hostip)

        try:
            self.__cur.execute(update_host_netstatus_sql)
            self.__conn.commit()
        except Exception as e:
            self.__conn.rollback()
            print(e)

    def update_host_internalmem(self, hostip, size):
        update_host_internalmem_sql = "update host set internal_memory_size=\'%s\' where ip=\'%s\'" % (size, hostip)

        try:
            self.__cur.execute(update_host_internalmem_sql)
            self.__conn.commit()
        except Exception as e:
            self.__conn.rollback()
            print(e)

    def update_host_cpustatus(self, hostip, size):
        update_host_cpustatus_sql = "update host set cpu_status=\'%s\' where ip=\'%s\'" % (size, hostip)

        try:
            self.__cur.execute(update_host_cpustatus_sql)
            self.__conn.commit()
        except Exception as e:
            self.__conn.rollback()
            print(e)

    def select_host_all(self):
        select_host_all_sql = "select * from host"

        val = []
        try:
            results = self.__cur.execute(select_host_all_sql)
            self.__conn.commit()
            for row in results:
                val.append(row)
            return val
        except Exception as e:
            print(e)

    def select_host_ipall(self, hostip):
        select_host_ipall_sql = "select * from host where ip=\'%s\'" % hostip

        val = []
        try:
            results = self.__cur.execute(select_host_ipall_sql)
            self.__conn.commit()
            for row in results:
                val.append(row)
            return val
        except Exception as e:
            print(e)

    def select_host_portstatus(self, hostip, port=1):
        if port == 1:
            select_host_port_sql = "select port_1_status from host where ip=\'%s\'" % hostip
        else:
            select_host_port_sql = "select port_2_status from host where ip=\'%s\'" % hostip

        val = []
        try:
            results = self.__cur.execute(select_host_port_sql)
            self.__conn.commit()
            for row in results:
                val.append(row[0])
            return val
        except Exception as e:
            print(e)

    def select_host_netstatus(self, hostip):
        select_host_netstatus_sql = "select net_status from host where ip=\'%s\'" % hostip

        val = []
        try:
            results = self.__cur.execute(select_host_netstatus_sql)
            self.__conn.commit()
            for row in results:
                val.append(row[0])
            return val
        except Exception as e:
            print(e)

    def select_host_internalmem(self, hostip):
        select_host_internalmem_sql = "select internal_memory_size from host where ip=\'%s\'" % hostip

        val = []
        try:
            results = self.__cur.execute(select_host_internalmem_sql)
            self.__conn.commit()
            for row in results:
                val.append(row[0])
            return val
        except Exception as e:
            print(e)

    def select_host_cpustatus(self, hostip):
        select_host_cpustatus_sql = "select cpu_status from host where ip=\'%s\'" % hostip

        val = []
        try:
            results = self.__cur.execute(select_host_cpustatus_sql)
            self.__conn.commit()
            for row in results:
                val.append(row[0])
            return val
        except Exception as e:
            print(e)


class packet_table:
    def __init__(self):
        try:
            self.__conn = sqlite3.connect("../database/host_packet.db")
            self.__cur = self.__conn.cursor()
            self.__create_packet_table()
        except Exception as e:
            raise e

    def __create_packet_table(self):
        create_packet_table_sql = "create table packet(" \
                                  "    id_name varchar not null primary key unique ," \
                                  "    cluster_ip varchar not null ," \
                                  "    size varchar not null ," \
                                  "    status_ds varchar not null" \
                                  ");"

        try:
            self.__cur.execute(create_packet_table_sql)
            self.__conn.commit()
        except Exception as e:
            print(e)

    def close(self):
        self.__conn.close()

    def insert_packet(self, id_name, cluster_ip, size, status_ds):
        insert_packet_sql = "insert into packet values (\'%s\', \'%s\', \'%s\', \'%s\')" \
                            % (id_name, cluster_ip, size, status_ds)

        try:
            self.__cur.execute(insert_packet_sql)
            self.__conn.commit()
        except Exception as e:
            self.__conn.rollback()
            print(e)

    def update_packet_name(self, id_name):
        update_packet_name_sql = "update packet set id_name=\'%s\' where id_name=\'%s\'" % (id_name, id_name)

        try:
            self.__cur.execute(update_packet_name_sql)
            self.__conn.commit()
        except Exception as e:
            self.__conn.rollback()
            print(e)

    def update_packet_clusterip(self, id_name, cluster_ip):
        update_packet_clusterip_sql = "update packet set cluster_ip=\'%s\' where id_name=\'%s\'" \
                                      % (cluster_ip, id_name)

        try:
            self.__cur.execute(update_packet_clusterip_sql)
            self.__conn.commit()
        except Exception as e:
            self.__conn.rollback()
            print(e)

    def update_packet_size(self, id_name, size):
        update_packet_size_sql = "update packet set size=\'%s\' where id_name=\'%s\'" \
                                 % (size, id_name)

        try:
            self.__cur.execute(update_packet_size_sql)
            self.__conn.commit()
        except Exception as e:
            self.__conn.rollback()
            print(e)

    def update_packet_statusds(self, id_name, status_ds):
        update_packet_statusds_sql = "update packet set status_ds=\'%s\' where id_name=\'%s\'" \
                                     % (status_ds, id_name)

        try:
            self.__cur.execute(update_packet_statusds_sql)
            self.__conn.commit()
        except Exception as e:
            self.__conn.rollback()
            print(e)

    def select_packet_all(self):
        select_packet_all_sql = "select * from packet"

        val = []
        try:
            results = self.__cur.execute(select_packet_all_sql)
            self.__conn.commit()
            for row in results:
                val.append(row)
            return val
        except Exception as e:
            print(e)

    def select_packet_idname_all(self, id_name):
        select_packet_idname_all_sql = "select * from packet where id_name=\'%s\'" % id_name

        val = []
        try:
            results = self.__cur.execute(select_packet_idname_all_sql)
            self.__conn.commit()
            for row in results:
                val.append(row)
            return val
        except Exception as e:
            print(e)

    def select_packet_idname_by_ip(self, id_name):
        select_packet_idname_by_ip_sql = "select id_name from packet where cluster_ip=\'%s\'" % id_name

        val = []
        try:
            results = self.__cur.execute(select_packet_idname_by_ip_sql)
            self.__conn.commit()
            for row in results:
                val.append(row[0])
            return val
        except Exception as e:
            print(e)

    def select_packet_clusterip(self, cluster_ip):
        select_packet_clusterip_sql = "select cluster_ip from packet where id_name=\'%s\'" % cluster_ip

        val = []
        try:
            results = self.__cur.execute(select_packet_clusterip_sql)
            self.__conn.commit()
            for row in results:
                val.append(row[0])
            return val
        except Exception as e:
            print(e)

    def select_packet_size(self, id_name):
        select_packet_size_sql = "select size from packet where id_name=\'%s\'" % id_name

        val = []
        try:
            results = self.__cur.execute(select_packet_size_sql)
            self.__conn.commit()
            for row in results:
                val.append(row[0])
            return val
        except Exception as e:
            print(e)

    def select_packet_statusds(self, id_name):
        select_packet_statusds_sql = "select status_ds from packet where id_name=\'%s\'" % id_name

        val = []
        try:
            results = self.__cur.execute(select_packet_statusds_sql)
            self.__conn.commit()
            for row in results:
                val.append(row[0])
            return val
        except Exception as e:
            print(e)

    def delete_packet(self, id_name):
        delect_packet_sql = "delete from packet where id_name=\'%s\'" % id_name

        try:
            self.__cur.execute(delect_packet_sql)
            self.__conn.commit()
        except Exception as e:
            self.__conn.rollback()
            print(e)
